package com.boss.salary.service.impl;

import com.boss.asset.service.IAssetInfoService;
import com.boss.framework.exception.AppRuntimeException;
import com.boss.salary.constant.ApiElementConstant;
import com.boss.salary.dao.*;
import com.boss.salary.entity.AgencyInfo;
import com.boss.salary.service.IPersonBatchAddService;
import com.boss.salary.vo.AgencySalaryVO;
import com.boss.salary.vo.ProvinceInfoVo;
import com.boss.sys.utils.SysUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

import static com.boss.salary.parsingExcel.impl.ReadSalaryReportExcel.getCellValue;

@Service
public class PersonBatchAddImpl implements IPersonBatchAddService {


    @Autowired
    private IPersonInfoDao iPersonInfoDao;

    @Autowired
    private IAgencyInfoDao iAgencyInfoDao;

    @Autowired
    private IPersonExtDao iPersonExtDao;

    @Autowired
    private ISalaryBasedataDao iSalaryBasedataDao;

    @Autowired
    private IAssetInfoService assetInfoService;

    @Autowired
    private IPersonBatchAddDao personBatchAddDao;

    @Autowired
    private IAgencyVerifyDao agencyVerifyDao;

    @Autowired
    private IAgencyInfoDao agencyInfoDao;

    @Autowired
    private IAgencyExtDao agencyExtDao;


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void Read(MultipartFile file, String province, List<AgencySalaryVO> exccelColumnName, String agencyId) throws IOException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        List<String> slist= new ArrayList<>();
        //获取文件流
        BufferedInputStream fis = new BufferedInputStream(file.getInputStream());
        //获取文件名
        String fileName= file.getOriginalFilename();

        String substring = fileName.substring(fileName.lastIndexOf("."));
        //判断版本（07，03）
        Workbook workbook = ".xls".equals(substring) ? new HSSFWorkbook(fis) : new XSSFWorkbook(fis);
        try{
            readExcel(province,slist,exccelColumnName, fis, workbook, agencyId);
        }catch (AppRuntimeException e){
            throw e;
        }
    }



    private void readExcel(String province, List<String> slist, List<AgencySalaryVO> exccelColumnName, BufferedInputStream fis, Workbook workbook, String agencyId) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException, IOException {
        List<ProvinceInfoVo> provinceInfoVos = new ArrayList<>();
        List<String> agencyCodes = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        if (rowTitle!=null) {
            //获取有效标题头
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    String cellValue = cell.getStringCellValue();
                    for (int i = 0; i < exccelColumnName.size(); i++) {
                        if(exccelColumnName.get(i).getSalaryName().equals(cellValue)){
                            slist.add(exccelColumnName.get(i).getColumnName());
                        }
                    }
                }
            }
            if(slist.size()!=cellCount-13){
                throw new AppRuntimeException("存在无法读取的工资项");
            }
        }
        //根据单位ID查询工资月份
        String salaryMonth = agencyVerifyDao.getSalaryMonth(province,agencyId);
        //根据单位ID查询单位CODE
        AgencyInfo agencyInfo = agencyInfoDao.getNameById(agencyId);
        //根据区划查找所有身份证号
        List<String> getIdenNos = iPersonInfoDao.getIdenNos(province);
        //根据区划查找所有银行卡号
        List<String> getSalaCardNos = iPersonExtDao.getSalaCardNos(province);
        //根据区划查找所有单位编码
        List<String> getAgencyCodes = iAgencyInfoDao.getAgencyCodes(province);
        //根据区划查找所有工资类型编码
        List<String> getSalaryCodes = iSalaryBasedataDao.getSalaryCodes(province);
        // 获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount ; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            ProvinceInfoVo provinceInfoVo = new ProvinceInfoVo();
            if (rowData!=null){
                // 读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
                    if(cellNum==0){
                        Cell cell = rowData.getCell(cellNum);
                        String agencyName=getCellValue(cell).toString();
                        provinceInfoVo.setAgencyName(agencyName);
                    }
                    if(cellNum==1){
                        Cell cell = rowData.getCell(cellNum);
                        String agencyCode=getCellValue(cell).toString();
                        String[] array=agencyCode.split("\\.");
                        agencyCode=array[0];
                        if(agencyInfo.getAgencyCode().equals(agencyCode)){
                            if("0".equals(agencyCode)){
                                agencyCode=null;
                            }
                            provinceInfoVo.setAgencyCode(agencyCode);
                        }else {
                            throw new AppRuntimeException("请输入本单位的单位编码!");
                        }
                    }
                    if(cellNum==2){
                        Cell cell = rowData.getCell(cellNum);
                        String deptName=getCellValue(cell).toString();
                        provinceInfoVo.setDeptName(deptName);
                    }
                    if(cellNum==3){
                        Cell cell = rowData.getCell(cellNum);
                        String idenNo=getCellValue(cell).toString();
                        // boolean result=idenNo.matches("^[1-9]\\d{5}(18|19|([23]\\d))\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$");
                        // boolean result1=idenNo.matches("^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{2}$");x//boolean result=idenNo.matches("((^[1-9]\\d{5}(18|19|([23]\\d))\\d{2}(((0[13578]|10|12)(0[1-9]|[12]\\d|3[01]))|((0[469]|11)(0[1-9]|[12]\\d|30))|(02(0[1-9]|[12]\\d)))\\d{3}[0-9Xx]$)|(^[1-9]\\d{5}\\d{2}(((0[13578]|10|12)(0[1-9]|[12]\\d|3[01]))|((0[469]|11)(0[1-9]|[12]\\d|30))|(02(0[1-9]|[12]\\d)))\\d{3}$))|(^[\\u4E00-\\u9FA5](字第)([0-9a-zA-Z]{4,10})(号?)$)");
                        // if(result == true||result1==true){
                        if(idenNo.length()>14 && idenNo.length()<19){
                            provinceInfoVo.setIdenNo(idenNo);
                        }else {
                            throw new AppRuntimeException(idenNo+"身份证号输入有误!");
                        }
                        // }else {

                        // }
                    }
                    if(cellNum==4){
                        Cell cell = rowData.getCell(cellNum);
                        String personName=getCellValue(cell).toString();
                        provinceInfoVo.setPersonName(personName);
                    }
                    if(cellNum==5){
                        Cell cell = rowData.getCell(cellNum);
                        String salaCardBank=getCellValue(cell).toString();
                        provinceInfoVo.setSalaCardBank(salaCardBank);
                    }
                    if(cellNum==6){
                        Cell cell = rowData.getCell(cellNum);
                        String salaCardNo=getCellValue(cell).toString();
                        // boolean result=salaCardNo.matches("^([1-9]{1})(\\d{14}|\\d{18})$");
                        if(!StringUtils.isEmpty(salaCardNo)){
                            provinceInfoVo.setSalaCardNo(salaCardNo);
                        }else {
                            throw new AppRuntimeException("银行卡号输入有误");
                        }

                    }
                    if(cellNum==7){
                        Cell cell = rowData.getCell(cellNum);
                        String personnelCode=getCellValue(cell).toString();
                        boolean result=personnelCode.matches("[a-zA-Z\\u4e00-\\u9fa5][a-zA-Z0-9\\u4e00-\\u9fa5]+");
                        // if(result == true){
                        if(personnelCode.length()<38 || null==cell){
                            provinceInfoVo.setPersonnelCode(personnelCode);
                        }else {
                            throw new AppRuntimeException("人员编码超出长度");
                        }
//                        }else {
//                            throw new AppRuntimeException("人员编码不可以输入汉字");
//                        }

                    }
                    if(cellNum==8){
                        Cell cell =rowData.getCell(cellNum);
                        String showOrder=getCellValue(cell).toString();
                        boolean result=showOrder.matches("^[0-9]*$");
                        if(result == true){
                            if(showOrder.length()<10 || null==cell){
                                provinceInfoVo.setShowOrder(showOrder);
                            }else {
                                throw new AppRuntimeException("人员排序超出长度");
                            }
                        }else {
                            throw new AppRuntimeException("人员排序请输入数字");
                        }
                    }
                    if(cellNum==9){
                        Cell cell = rowData.getCell(cellNum);
                        String sex=getCellValue(cell).toString();
                        if(sex.equals("男")){
                            sex="1";
                        }else if(sex.equals("女")){
                            sex="2";
                        }else {
                            throw new AppRuntimeException("性别输入错误输入正确性别");
                        }
                        provinceInfoVo.setSex(sex);
                    }
                    if(cellNum==10){
                        Cell cell = rowData.getCell(cellNum);
                        String salaryName=getCellValue(cell).toString();
                        provinceInfoVo.setSalaryName(salaryName);
                    }
                    if(cellNum==11){
                        Cell cell = rowData.getCell(cellNum);
                        String meritPayCode=getCellValue(cell).toString();
                        String[] array=meritPayCode.split("\\.");
                        meritPayCode=array[0];
                        provinceInfoVo.setMeritPayCode(meritPayCode);
                    }
                    if(cellNum==12){
                        Cell cell = rowData.getCell(cellNum);
                        String state=getCellValue(cell).toString();
                        if(state.equals("在职")){
                            state="3";
                        }else if(state.equals("离退休")){
                            state="1";
                        }else {
                            throw new AppRuntimeException("在职状态为空或者输入有误,请输入：在职或离退休");
                        }
                        provinceInfoVo.setState(state);
                    }
                    if(cellNum>12){
                        Cell cell = rowData.getCell(cellNum);
                        Class<?> clazz = provinceInfoVo.getClass();
                        String strCell;
                        try {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()== CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }

                        } catch (IllegalStateException e) {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()==CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }
                        }
                        Method setMethod = clazz.getDeclaredMethod("set" +
                                slist.get(cellNum-13).substring(0, 1).toUpperCase() +  slist.get(cellNum-13).substring(1),Double.class);
                        //setMethod.invoke(salaryMonthEndVO,Double.valueOf(getCellValueByCell(cell)));
                        setMethod.invoke(provinceInfoVo,Double.valueOf(strCell));
                    }
                }
                String personId = SysUtils.generateGuid();
                agencyCodes.add(provinceInfoVo.getAgencyCode());
                provinceInfoVo.setMofDivCode(province);
                provinceInfoVo.setPersonId(personId);
                provinceInfoVo.setSalaryMonth(salaryMonth);
                provinceInfoVos.add(provinceInfoVo);
            }
        }
        //根据单位ID查找银行CODE
        String agencyBankCode = agencyExtDao.selectAgencyBankCode(agencyId);
        agencyBankCode = agencyBankCode.replace("[","");
        agencyBankCode = agencyBankCode.replace("]","");
        List<String> agencyBankCodes = Arrays.asList(agencyBankCode.split(","));
        //根据单位编码查找单位ID
        List<Map<String, Object>> getAgencyIds = iAgencyInfoDao.getAgencyId(agencyCodes,province);
        //查找工资发卡银行及对应的编码
        List<Map<String,String>> bankNameAndCode = assetInfoService.getBaseDataSelect(ApiElementConstant.SALBANK, province);
        //根据工资发卡银行获取对应编码
        Map<String,String> getBankCode = new HashMap<>();
        String code = null;
        String name = null;
        for (int i = 0; i < bankNameAndCode.size(); i++) {
            for (int j = 0; j < 2; j++) {
                code = bankNameAndCode.get(i).get("code");
                name = bankNameAndCode.get(i).get("name");
            }
            getBankCode.put(name,code);
        }

        //工资发卡银行判断
        List<Integer> salaCardBanks = new ArrayList<>();
        int count = 0;
        for (int i = 0; i < provinceInfoVos.size(); i++) {
            if(getBankCode.containsKey(provinceInfoVos.get(i).getSalaCardBank())&&agencyBankCodes.contains(getBankCode.get(provinceInfoVos.get(i).getSalaCardBank()))){
                count++;
                provinceInfoVos.get(i).setSalaCardBank(getBankCode.get(provinceInfoVos.get(i).getSalaCardBank()));
            }else { salaCardBanks.add(i);}
        }
        if(provinceInfoVos.size()!=count){
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < salaCardBanks.size(); i++) {
                String personName = provinceInfoVos.get(salaCardBanks.get(i)).getPersonName();
                if(i==salaCardBanks.size()-1){
                    sb.append(personName).append(" ");
                }else{
                    sb.append(personName).append("，").append(" ");
                }
                sb.append(personName).append(" ");
            }
            String str = sb.toString();
            throw new AppRuntimeException(str+"的工资发卡银行有误");
        }
        //身份证是否有重复判断
        boolean isIdenNos = false;
        List<String> idenNos = new ArrayList<>();
        List<ProvinceInfoVo> provinceInfoList = new ArrayList<>();
        for(ProvinceInfoVo item1:provinceInfoVos){
            if(provinceInfoList.size()>0){
                for(ProvinceInfoVo provinceInfoVo:provinceInfoList){
                    if(provinceInfoVo.getIdenNo().equals(item1.getIdenNo())){
                        throw new AppRuntimeException(provinceInfoVo.getIdenNo()+"身份证号重复");
                    }
                }
            }
            provinceInfoList.add(item1);
            for(String item2:getIdenNos){
                if((item1.getIdenNo()).equals(item2)){
                    isIdenNos = true;
                    idenNos.add(item1.getIdenNo());
                }
            }
        }
        if(isIdenNos){
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < idenNos.size(); i++){
                if(i==idenNos.size()-1){
                    sb.append(idenNos.get(i)).append(" ");
                }else{
                    sb.append(idenNos.get(i)).append("，").append(" ");
                }
            }
            String str = sb.toString();
            throw new AppRuntimeException("身份证号"+str+"已存在");
        }

        //银行卡号是否有重复判断
        boolean isSalaCardNos = false;
        List<String> salaCardNos = new ArrayList<>();
        List<ProvinceInfoVo> provinceInfoLists = new ArrayList<>();
        for(ProvinceInfoVo item1:provinceInfoVos){
            if(provinceInfoLists.size()>0){
                for(ProvinceInfoVo provinceInfoVo:provinceInfoList){
                    if(provinceInfoVo.getSalaCardNo().equals(item1.getSalaCardNo())){
                        throw new AppRuntimeException(provinceInfoVo.getSalaCardNo()+"银行卡号重复");
                    }
                }
            }
            provinceInfoList.add(item1);
            for(String item2:getSalaCardNos){
                if((item1.getSalaCardNo()).equals(item2)){
                    isSalaCardNos = true;
                    salaCardNos.add(item1.getSalaCardNo());
                }
            }
        }
        if(isSalaCardNos){
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < salaCardNos.size(); i++){
                if(i==salaCardNos.size()-1){
                    sb.append(salaCardNos.get(i)).append(" ");
                }else{
                    sb.append(salaCardNos.get(i)).append("，").append(" ");
                }
            }
            String str = sb.toString();
            throw new AppRuntimeException("银行卡号"+str+"已存在");
        }

        //单位编码判断
        List<Integer> agencyCodeList = new ArrayList<>();
        int count2 = 0;
        int count3;
        boolean flag1;
        for (int i = 0; i < provinceInfoVos.size(); i++) {
            count3=0;
            flag1=true;
            for (int j = 0; j < getAgencyCodes.size(); j++){
                count3++;
                if(getAgencyCodes.get(j).equals(provinceInfoVos.get(i).getAgencyCode())){
                    count2++;flag1=false;
                }else if(flag1&&(count3==getAgencyCodes.size())){ agencyCodeList.add(i);}
            }
        }
        if(provinceInfoVos.size()!=count2){
            String agencyCode;
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < agencyCodeList.size(); i++) {
                agencyCode = provinceInfoVos.get(agencyCodeList.get(i)).getAgencyCode();
                if(i==agencyCodeList.size()-1){
                    sb.append(agencyCode).append(" ");
                }else{
                    sb.append(agencyCode).append("，").append(" ");
                }
            }
            String str = sb.toString();
            throw new AppRuntimeException("单位编码"+str+"有误");
        }
        //工资类型编码判断
        List<Integer> salaryCodes = new ArrayList<>();
        int count4 = 0;
        int count5;
        boolean flag2;
        for (int i = 0; i < provinceInfoVos.size(); i++) {
            count5=0;
            flag2=true;
            for (int j = 0; j < getSalaryCodes.size(); j++){
                count5++;
                if(getSalaryCodes.get(j).equals(provinceInfoVos.get(i).getMeritPayCode())){
                    count4++;flag2 = false;
                }else if(flag2&&(count5==getSalaryCodes.size())){ salaryCodes.add(i);}
            }
        }
        if(provinceInfoVos.size()!=count4){
            StringBuilder sb = new StringBuilder();
            String personName;
            for (int i = 0; i < salaryCodes.size(); i++) {
                personName = provinceInfoVos.get(salaryCodes.get(i)).getPersonName();
                if(i==salaryCodes.size()-1){
                    sb.append(personName).append(" ");
                }else{
                    sb.append(personName).append("，").append(" ");
                }
            }
            String str = sb.toString();
            throw new AppRuntimeException(str+"的工资类型编码有误");
        }
        //给数据源provinceInfoVos赋值agencyId
        for(int i=0;i<provinceInfoVos.size();i++){
            //用code 做对比 如果相同插入agencyid
            for (Map map:getAgencyIds){
                if(map.get("agency_code").equals(provinceInfoVos.get(i).getAgencyCode())){
                    provinceInfoVos.get(i).setAgencyId(map.get("id").toString());
                }
            }
        }
        personBatchAddDao.batchSaveProvinceInfo(provinceInfoVos);
        fis.close();
    }






}
